package database;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.locks.ReentrantReadWriteLock;

import org.apache.derby.drda.NetworkServerControl;

import quantity.Location;
import quantity.Quantity;
import quantity.Quantity.QuantityType;
import quantity.QuantityList;

import book.Book;

import term.Term;
import term.dept.Department;
import term.dept.course.Course;
import term.dept.course.section.Enrollment;
import term.dept.course.section.Instructor;
import term.dept.course.section.Section;

import Util.Util;
import adoption.Adoption;

public class Database {

	private Connection con;
	private PreparedStatement insertBook, getBook,
							  insertSection, updateSection,
							  insertAdoption, updateAdoption,
							  insertTerm, updateTerm,
							  insertQty, updateQty;
	private ReentrantReadWriteLock ibLock = new ReentrantReadWriteLock(), gbLock = new ReentrantReadWriteLock(),
								   isLock = new ReentrantReadWriteLock(), usLock = new ReentrantReadWriteLock(),
								   iaLock = new ReentrantReadWriteLock(), uaLock = new ReentrantReadWriteLock(),
								   itLock = new ReentrantReadWriteLock(), utLock = new ReentrantReadWriteLock();

	/**
	 * Represents the database at Connection c.
	 * 
	 * @param connection
	 * @param term
	 * @throws Exception 
	 */
	public Database(String connection) throws Exception {
		long t0 = System.currentTimeMillis();
		NetworkServerControl server = new NetworkServerControl();
		server.start(new PrintWriter(System.out));
		con = DriverManager.getConnection(connection);
		Util.printStatusString("db connection time: "+(System.currentTimeMillis() - t0));
	}
	
	public Map<String,QuantityList> getQtys() {
		HashMap<String,QuantityList> qtys = new HashMap<String,QuantityList>();

		try {
			Statement stmt = con.createStatement();
			stmt.execute("select * from quantities");
			
			ResultSet rslt = stmt.getResultSet();
			while (rslt.next()) {
				String inv_num = rslt.getString("inv_num"),
					   type = rslt.getString("type"),
					   loc = rslt.getString("loc");
				boolean used = rslt.getBoolean("used");
				int qty = rslt.getInt("qty");
				
				QuantityList ql = qtys.get(inv_num);
				if (ql == null)
					ql = qtys.put(inv_num, new QuantityList());
				
				Quantity q = new Quantity(qty,QuantityType.getQT(type, !used));
				q.getType().setLocation(Location.get(loc));
				
				ql.add(q);
			}
			
			return qtys;
		} catch (SQLException e) {
			try {
				prepareQtyStmts();
				return getQtys();
				
			} catch (SQLException ex) {
				ex.printStackTrace();
				return getQtys();
			}
		}
	}
	
	public Book getBook(String inv_num) throws SQLException {
		
		getBook.setString(1, inv_num);
		getBook.execute();
		
		ResultSet rslt = getBook.getResultSet();
		
		Book out;
		if (!rslt.next())
			throw new SQLException("Book "+inv_num+" does not exist.");
		else {
			String author = rslt.getString("author"),
				   title = rslt.getString("title"),
				   isbn = rslt.getString("isbn"),
				   ed = rslt.getString("ed");
			out = new Book(author,title,Long.valueOf(isbn),ed,inv_num);
		}
		
		return out;
	}
	
	public ArrayList<Term> getTerms(){
		ArrayList<Term> out = new ArrayList<Term>();
		
		Statement stmt;
		try {
			stmt = con.createStatement();
			stmt.execute("select * from terms");
			
			ResultSet rslt = stmt.getResultSet();
			
			while (rslt.next()) {
				Term tm = Term.parse(rslt.getString("term"));
				
				tm.setDates(rslt.getDate("s_date"), rslt.getDate("e_date"));
				out.add(tm);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			Statement sttmt;
			try {
				sttmt = con.createStatement();
			sttmt.execute("create table terms (s_date date, e_date date, term varchar(15), primary key (term))");
			
			return getTerms();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		
		return out;
	}

	/**
	 * Populates local data structures with information
	 * associated with the given term from the database.
	 * 
	 * Must have a populated book db table!
	 * 
	 * @param t
	 * @return
	 * @throws SQLException
	 */
	public Term getTerm(Term t) {
		try {
		String crsTbl, adptTbl;
		prepareStmts(crsTbl = t.courseTableName(), adptTbl = t.adoptionTableName());
		
		Statement stmt = con.createStatement();
		stmt.execute("select * from "+crsTbl);
		PreparedStatement pstmt = prepare("select * from "+adptTbl+" where sec_id=?");
		
		ResultSet cRslt = stmt.getResultSet();
		int max_c_id = 0, max_a_id = 0;
		
		while (cRslt.next()) {	// for each course record
			Department d = t.getDept(Department.Code.getCode(cRslt.getString("dept")));
			Course c = d.addCourse(cRslt.getInt("course"),cRslt.getString("course_name"));
			Instructor in = Instructor.get(cRslt.getString("inst"));
			Section s = c.addSection(cRslt.getString("sec"), in, cRslt.getInt("sec_id"));
			s.setEnrollments(cRslt.getInt("prev_e"), cRslt.getInt("est_e"), cRslt.getInt("cur_e"), cRslt.getInt("cap_e"));
			
			max_c_id = Math.max(max_c_id, s.getSec_id());
			
			//System.out.println(s.getFullName());
			
			//System.out.println(s.getFullName()+" read in. Adoptions:");
			pstmt.setInt(1, s.getSec_id());
			pstmt.execute();
			
			ResultSet aRslt = pstmt.getResultSet();
			while (aRslt.next()) { // for each adoption record
				Book b = getBook(aRslt.getString("inv_num"));
				Adoption a = new Adoption(s, b, aRslt.getString("status"), aRslt.getInt("adoption_id"));
				s.addAdoption(a);
				t.addAdoption(a);
				
				max_a_id = Math.max(max_a_id, a.getID());
			}
		}
		
		Section.setNext_id(max_c_id);
		Adoption.setNextID(max_a_id);
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return t;
	}

	// "update ? set inst=?, cap_e=?, prev_e=?, est_e=?, cur_e=? where dept=? and course=? and section=?"
	private void prepareSectionStmts(String cTN) throws SQLException {
		try {
			insertSection = prepare("insert into " + cTN
					+ " values (?,?,?,?,?,?,?,?,?,?,?)");
			updateSection = prepare("update "
					+ cTN
					+ " set inst=?, cap_e=?, prev_e=?, est_e=?, cur_e=? where sec_id=?");
		} catch (SQLException e) {
			// e.printStackTrace();

			Statement stmt = con.createStatement();
			stmt.execute("create table "
					+ cTN
					+ " (dept varchar(4), course int, course_name varchar(50), sec varchar(4), sec_name varchar(50), sec_id int, inst varchar(50), cap_e int, prev_e int, est_e int, cur_e int, primary key (sec_id))");
			prepareSectionStmts(cTN);
			return;
		}
	}
	
	private void prepareAdoptionStmts(String aTN) throws SQLException {
		try {
			insertAdoption = prepare("insert into "
					+ aTN + " values (?,?,?,?)");
			updateAdoption = prepare("update " + aTN
					+ " set inv_num=?, status=? where adoption_id=?");
		} catch (SQLException e) {
			// e.printStackTrace();

			Statement stmt = con.createStatement();
			stmt.execute("create table "
					+ aTN
					+ " (adoption_id int, sec_id int, inv_num varchar(7), status varchar(7), primary key (adoption_id))");
			prepareAdoptionStmts(aTN);
			return;
		}
	}
	
	private void prepareBookStmts() throws SQLException {
		try {
			insertBook = prepare("insert into books values (?,?,?,?,?)");
			getBook = prepare("select * from books where inv_num=?");
		} catch (SQLException e) {
			Statement stmt = con.createStatement();
			stmt.execute("create table books (author varchar(25), title varchar(75), isbn varchar(13), ed varchar(6), inv_num varchar(7) not null, primary key (inv_num))");
			prepareBookStmts();
			return;
		}
	}
	
	private void prepareTermStmts() throws SQLException {
		try {
			insertTerm = prepare("insert into terms values (?,?,?)");
			updateTerm = prepare("update terms set s_date=?, e_date=? where term=?");
		} catch (SQLException e) {
			//e.printStackTrace();
			Statement stmt = con.createStatement();
			stmt.execute("create table terms (s_date date, e_date date, term varchar(15), primary key (term))");
			prepareTermStmts();
			return;
		}
	}
	
	private void prepareQtyStmts() throws SQLException {
		try {
			insertQty = prepare("insert into quantities values (?,?,?,?,?)");
			updateQty = prepare("update quantities set qty=?, loc=? where inv_num=?");
		} catch (SQLException e) {
			e.printStackTrace();
			con.createStatement().execute("create table quantities (inv_num varchar(7), used boolean, qty integer, type varchar(20), loc varchar(4), primary key (inv_num, used, type))");
			prepareQtyStmts();
			return;
		}
	}
	
	private void prepareStmts(String courseTableName, String adoptionTableName) throws SQLException {
		
		if (insertSection == null || updateSection == null)
			prepareSectionStmts(courseTableName);
	
		if (insertAdoption == null || updateAdoption == null)
			prepareAdoptionStmts(adoptionTableName);
		
		if (insertBook == null || getBook == null)
			prepareBookStmts();
		
		if (insertTerm == null || updateTerm == null)
			prepareTermStmts();
		
		if (insertQty == null || updateQty == null)
			prepareQtyStmts();
	}

	public void saveTerm(Term term) {
		System.out.println("saving term " + term.toString());
		ArrayList<SaveThread> threads = new ArrayList<SaveThread>();
		
		try {
			prepareStmts(term.courseTableName(),term.adoptionTableName());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		for (Department d : term.getDepts())
			threads.add(new SaveThread(d));
		
		for (SaveThread s : threads)
			s.start();
		
		try {

			insertTerm.setDate(1,term.getStartDate());
			insertTerm.setDate(2,term.getEndDate());
			insertTerm.setString(3, term.toString());
			
			insertTerm.execute();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			//e1.printStackTrace();
			//
			// Term already exists.
		}
		
		for (SaveThread s : threads)
			try {
				s.join();
				System.out.println("joined thread: "+s.getName());
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println("Error with joining SaveThread: "+s.getName());
			}
	}

	private PreparedStatement prepare(String sql) throws SQLException {
		return con.prepareStatement(sql);
	}
	
	private class SaveThread extends Thread {
		
		Department d;
		
		public SaveThread(Department dept) {
			super("saving department "+dept.toString());
			
			d = dept;
		}
		
		public void run() {
			for (Course c : d.getCourses().values())
				for (Section s : c.getSections().values())
					try {
						saveSection(s);
					} catch (SQLException e) {
						e.printStackTrace();
					}
		}
		/**
		 * Saves the given section, its adoptions, and its books into the db.
		 * @param s
		 * @throws SQLException
		 */
		private void saveSection(Section s) throws SQLException {
			if (s.getDept().getCode() == null)
				System.out.println("code error.");
			if (s.getDept().getCode().getCode() == null)
				System.out.println("code code error.");
			if (!s.isChanged())
				return;
			
			if (!insertSection(s.getDept().getCode().getCode(),s.getCourse().getNumber(), s))
				updateSection(s.getDept().getCode().getCode(), s.getCourse().getNumber(), s);
			
			for (Adoption a : s.getAdoptions()) {
				
				saveBook(a.getBook());
				
				if (!insertAdoption(a))
					updateAdoption(a);
			}
		}

		private boolean insertAdoption(Adoption a){
			
			//System.out.println("waiting to save adoption "+a.getID());
			iaLock.writeLock().lock();
			//System.out.println("lock acquired: saving adoption "+a.getID());
			try {
				insertAdoption.setInt(1, a.getID());
				insertAdoption.setInt(2, a.getSection().getSec_id());
				insertAdoption.setString(3, a.getBook().getInv_num());
				insertAdoption.setString(4, a.getStatus());
				
				insertAdoption.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				iaLock.writeLock().unlock();
				//System.out.println("lock released: "+a.getID());
				return false;
			}
			iaLock.writeLock().unlock();
			//System.out.println("lock released: saved adoption "+a.getID());
			return true;
			//System.out.println("inserting: "+a.getID()+" "+a.getSection().getSec_id()+" "+a.getBook().getInv_num()+" "+a.getStatus());

			// (adoption_id int, sec_id int, inv_num varchar(7), status varchar(3),
		}

		// "update ? set inst=?, cap_e=?, prev_e=?, est_e=?, cur_e=? where dept=? and course=? and section=?"
		
		private boolean updateAdoption(Adoption a){
		
			uaLock.writeLock().lock();
			try {
				updateAdoption.setString(1, a.getBook().getInv_num());
				updateAdoption.setString(2, a.getStatus());
				updateAdoption.setInt(3, a.getID());
				
				updateAdoption.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				uaLock.writeLock().unlock();
				return false;
			}
			uaLock.writeLock().unlock();
			return true;
			// set inv_num=?, status=? where adoption_id=?
			// (adoption_id int, sec_id int, inv_num varchar(7), status varchar(3),
		}

		private boolean insertSection(String d, int c, Section s){

			Enrollment en = s.getEnrollment();
			isLock.writeLock().lock();
			try {
				insertSection.setString(1, d);
				insertSection.setInt(2, c);
				insertSection.setString(3, s.getCourse().getName());
				insertSection.setString(4, s.getID());
				insertSection.setString(5, "");
				insertSection.setInt(6, s.getSec_id());
				insertSection.setString(7, s.getInst().getName());
				insertSection.setInt(8, en.getCap());
				insertSection.setInt(9, en.getPrev());
				insertSection.setInt(10, en.getEst());
				insertSection.setInt(11, en.getCur());
				
				insertSection.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				isLock.writeLock().unlock();
				return false;
			}
			isLock.writeLock().unlock();
			return true;
			//dept varchar(4), course int, course_name varchar(50), sec varchar(4), sec_name varchar(50),
			//sec_id int, inst varchar(50), cap_e int, prev_e int, est_e int, cur_e int,
		}

		// "update ? set inst=?, cap_e=?, prev_e=?, est_e=?, cur_e=? where dept=? and course=? and section=?"
		
		private boolean updateSection(String d, int c, Section s){
		
			Enrollment en = s.getEnrollment();
			
			usLock.writeLock().lock();
			try {
				updateSection.setString(1, s.getInst().getName());
				updateSection.setInt(2, en.getCap());
				updateSection.setInt(3, en.getPrev());
				updateSection.setInt(4, en.getEst());
				updateSection.setInt(5, en.getCur());
				updateSection.setInt(6, s.getSec_id());
				
				updateSection.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				usLock.writeLock().unlock();
				return false;
			}
			usLock.writeLock().unlock();
			return true;
			//set inst=?, cap_e=?, prev_e=?, est_e=?, cur_e=? where sec_id=?
		}
		
		private boolean saveBook(Book b){
		
			try {
				//TODO check primary key contraint.
				//Is the book in the DB?
				ibLock.writeLock().lock();
				insertBook.setString(1, b.getAuthor());
				insertBook.setString(2, b.getTitle());
				insertBook.setLong(3, b.getISBN());
				insertBook.setString(4, b.getEdition().getEd());
				insertBook.setString(5, b.getInv_num());
				
				insertBook.execute();
				//no, it wasn't. book inserted
				//System.out.println("Inserted book: "+b.getInv_num());
				//into books (isbn, author, title, ed, price)
			}
			catch (SQLException e) {
				//yes. do nothing.
				ibLock.writeLock().unlock();
				return false;
				//Book.getInstance(ISBN);
			}
			ibLock.writeLock().unlock();
			return true;
		}
	}
}
